﻿using ConfExpert.Forms.Base;
using ConfExpert.Utility;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraPrinting;
using Extensions;
using System.Data;
using System.IO;
using System.Windows.Forms;

namespace ConfExpert.Forms.Araclar
{
    public partial class Consolidate2 : XForm
    {
        public Consolidate2()
        {
            InitializeComponent();
        }

        private void Consolidate2_Load(object sender, System.EventArgs e)
        {
            ds.Clear();
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
                if (openFileDialog1.OpenFile() != null)
                {
                    DataTable dt = ReadExcel(openFileDialog1.FileName);
                    if (dt == null)
                        return;

                    gridView1.Init(dt);
                    //gridView1.FormatAsNumber(1, "Tot Cost", "Tot Retail");
                    //gridView1.AddSummary("Tot Cost", "Tot Retail", "Total");
                    gridView1.BestFitColumns();


                    //string FilePath = Dialogs.SaveFile("Excel Document", "Excel Documents|*.xlsx");

                    //XlsxExportOptions opt = new XlsxExportOptions();
                    //opt.TextExportMode = TextExportMode.Value;

                    //gridView1.ExportToXlsx(FilePath, opt);

                    //System.Diagnostics.Process process = new System.Diagnostics.Process();
                    //process.StartInfo.FileName = FilePath;
                    //process.Start();

                }
        }

        /*
        public DataTable OldReadExcel(string filename)
        {
            if (filename.EndsWith(".xls"))
            {
                ShowMessage("Lütfen dosyayı XLSX formatında kaydedin");
                return null;
            }

            bool hasHeader = false;
            bool ColumnsInserted = false;
            DataTable table = new DataTable("x");
            using (var excelFile = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(filename))                
                    excelFile.Load(stream);                

                var startRow = hasHeader ? 2 : 1;
                foreach (ExcelWorksheet ws in excelFile.Workbook.Worksheets)
                    if (ws.Dimension != null)
                    {
                        if (!ColumnsInserted)
                        {
                            for (int i = 0; i < ws.Dimension.End.Column; i++)
                            {
                                string fld = ws.Cells[7, i + 1].Text;
                                table.Columns.Add(fld);
                            }

                            table.Columns.Add("SheetName");
                            ColumnsInserted = true;
                        }

                        for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                        {
                            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                            var row = table.NewRow();
                            foreach (var cell in wsRow)
                                row[cell.Start.Column - 1] = cell.Text;

                            row[ws.Dimension.End.Column] = ws.Name;

                            if (row[0].IsNumeric())
                                table.Rows.Add(row);
                        }
                    }
            }

            return table;
        }
        */


        public DataTable ReadExcel(string filename)
        {
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(filename);
            System.Data.DataSet dataSet = workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);

            bool firstrow = false;
            foreach (DataTable d in dataSet.Tables)
            {
                if (d.TableName.StartsWith("Sheet"))
                    continue;

                d.Columns.Add("SheetName", typeof(string));

                foreach (DataRow r in d.Rows)
                    r["SheetName"] = d.TableName;

                for (int i = d.Rows.Count-1; i >= 0; i--)
                    if (!(d.Rows[i][0].ToStr() == "Row" && !firstrow) && 
                        d.Rows[i][0].ToInt() == 0)
                        d.Rows.RemoveAt(i);

                firstrow = true;
                dataSet.Tables[0].Merge(d);
            }

            return dataSet.Tables[0];

        }
    }
}
